import pymysql

# 建立连接
connection = pymysql.connect(host='localhost', user='root', password='syh05', database='music_tj', port = 3306)
print(connection)

# 打印数据库
def print_database():
    with connection.cursor() as cursor:
        sql = "SELECT * FROM song_type"
        cursor.execute(sql)
        result = cursor.fetchall()
        for row in result:
            print(row)

# 新增一个方法

def insert_song_type(params):
    with connection.cursor() as cursor:
        sql = "INSERT INTO song_sheet (id, sheet_id) VALUES (null, %s)"
        cursor.execute(sql, params)
        connection.commit()
    print("插入成功")
def insert_song_sheet(params):
    with connection.cursor() as cursor:
        sql = "INSERT into song_sheet(id, sheet_id, NAME,ms,img,count,song_type_id,create_time) values(NULL, %s,%s,%s,%s,%s,%s,%s)"
        cursor.execute(sql, params)
        connection.commit()
    print("插入成功")

def query_song_sheet_list():
    with connection.cursor() as cursor:
        sql = "SELECT * FROM song_sheet"
        cursor.execute(sql)
        result = cursor.fetchall()
        music_sheet_list = []
        for music_sheet in result:
            music_dict = {
                'id': music_sheet[0],
                'sheet_id': music_sheet[1],
                'name': music_sheet[2],
                'desc': music_sheet[3],
                'img': music_sheet[4]
            }
            music_sheet_list.append(music_dict)
    return music_sheet_list



if __name__ == '__main__':

    list = query_song_sheet_list()
    print(f'歌单的长度是{len(list)}')
    for music_sheet in list:
        print(music_sheet)
    # print_database()
    # quary_song_sheet_list(('123456'))
    # print(quary_song_sheet_list)
    connection.close()
